package com.ftt.product.dao;

import com.ftt.DBUtile;
import com.ftt.Util;
import com.ftt.product.model.Order;
import com.ftt.product.model.Product;
import com.ftt.product.model.ShoppingCar;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    /** 获取商品列表 */
    public List<Product>getProductList(){
        List<Product> list = new ArrayList<>();
        Connection conn = DBUtile.getConnection();
        String sql = "select * from tb_product order by create_time desc limit 10";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
//            ArrayList list = new ArrayList();
            while (rs.next()) {
                Product p = new Product();
                p.setId(rs.getInt("id"));
                p.setName(rs.getString("name"));
                p.setDescription(rs.getString("description"));
                p.setMarket_price((float) rs.getDouble("market_price"));
                p.setSell_price((float) rs.getDouble("sell_price"));
                p.setPicture(rs.getString("picture"));
                list.add(p);
                p = null;
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,rs);
        }
        return list;
    }
    /** 根据商品编号来获取商品信息 */
    public Product getProductByID(String id){
        Connection conn=DBUtile.getConnection();
        String sql="select * from tb_product where id=?";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,id);
            rs= ps.executeQuery();
            if (rs.next()){
                Product product=new Product();
                product.setDescription(rs.getString("description"));
                product.setName(rs.getString("name"));
                product.setId(rs.getInt("id"));
                product.setMarket_price(rs.getFloat("market_price"));
                product.setSell_price(rs.getFloat("sell_price"));
                product.setPicture(rs.getString("picture"));
                return product;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,rs);
        }
        return null;
    }
    /** 将商品添加到购物车 */
    public int addCar(String customerId, String productId, String number){
        Connection conn=DBUtile.getConnection();
        String sql="INSERT INTO `tb_car` (`customerid`,`productid`,`number`) VALUES(?,?,?)";
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,customerId);
            ps.setString(2,productId);
            ps.setString(3,number);
            int result= ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,null);
        }


    }
    /** 更新购物车商品数量 */
    public int updateCar(String customerId,String productId,String number){
        Connection conn=DBUtile.getConnection();
        String sql="UPDATE `tb_car` set `number`=? where `customerid`=? and `productid`=?";
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1,number);
            ps.setString(2,customerId);
            ps.setString(3,productId);

            int result= ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,null);
        }
    }
    /** 判断当前用户是否已填写此商品 */
    public int isExists(String customerId,String productId ){
        Connection conn=DBUtile.getConnection();
        String sql="select * from tb_car where `customerid`=? and `productid`=?";
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps =conn.prepareStatement(sql);
            ps.setString(1,customerId);
            ps.setString(2,productId);
            rs= ps.executeQuery();
            if (rs.next()){
                int number=rs.getInt("number");
                return  number;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,rs);
        }
        return 0;
    }
    //
    public List<ShoppingCar> getShoppingCarInfo(String customerId){
        Connection conn=DBUtile.getConnection();
        String sql="select * from tb_car join tb_product on tb_car.productid=tb_product.id where tb_car.customerid=?";
        PreparedStatement ps=null;
        ResultSet rs=null;
        List<ShoppingCar> list=new ArrayList<>();
        try {
            ps= conn.prepareStatement(sql);
            //修改不传参
            ps.setString(1, customerId);
            rs= ps.executeQuery();
            while (rs.next()){
                ShoppingCar shoppingCar=new ShoppingCar();
                shoppingCar.setCustomerId(rs.getInt("customerid"));
                shoppingCar.setProductId(rs.getInt("productid"));
                shoppingCar.setDescription(rs.getString("description"));
                shoppingCar.setName(rs.getString("name"));
                shoppingCar.setPicture(rs.getString("picture"));
                shoppingCar.setNumber(rs.getInt("number"));
                shoppingCar.setSell_price(rs.getDouble("sell_price"));
                list.add(shoppingCar);
                shoppingCar=null;
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn,ps,rs);
        }
    }
    /** 根据会员联系方式及购物车列表来生成订单 */
    public int addOrder(String phone,String total_price, List<ShoppingCar> list){
        Connection conn = DBUtile.getConnection();
        String sql = "insert into tb_order (`order_id`,`total_price`,`mobile`) values(?,?,?)";
        PreparedStatement ps = null;
        String orderId = Util.createOrderId();
        int n = 0;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1,orderId);
            ps.setString(2,total_price);
            ps.setString(3,phone);
            n = ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn, ps, null);
        }
        if(n>0){
            Connection conn1 = DBUtile.getConnection();
            String sql1 = "INSERT INTO `tb_order_item`(`order_id`, `product_id`, `product_name`, `product_price`, `amount`) VALUES (?, ?, ?, ?, ?)";
            PreparedStatement ps1 = null;
            try {
                ps1 = conn1.prepareStatement(sql1);
                System.out.println(list);
                for (int i = 0; i < list.size(); i++) {
                    ShoppingCar shoppingCar = list.get(i);
                    ps1.setString(1,orderId);
                    ps1.setInt(2,shoppingCar.getProductId());
                    ps1.setString(3,shoppingCar.getName());
                    ps1.setDouble(4,shoppingCar.getSell_price());
                    ps1.setInt(5,shoppingCar.getNumber());
                    ps1.addBatch();
                }
                ps1.executeBatch();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                DBUtile.closeResource(conn1,ps,null);
            }
        }
        return 1;
    }
    /** 根据电话号码查找订单信息 */
    public List<Order> getProductOrder(long phone){
        Connection conn = DBUtile.getConnection();
        String sql = "SELECT * FROM tb_order JOIN tb_order_item on tb_order.order_id = tb_order_item.order_id WHERE mobile = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Order> list = new ArrayList<>();
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1,phone);
            rs = ps.executeQuery();
            while (rs.next()){
                Order order = new Order();
                order.setOrder_id((rs.getInt("order_id")));
                order.setName(rs.getString("product_name"));
                order.setTotal_price(rs.getString("total_price"));
                order.setCreate_time(rs.getString("create_time"));
                order.setReal_name(rs.getString("real_name"));
                list.add(order);
            }
            System.out.println("数据库里的订单数据为："+list);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtile.closeResource(conn, ps, rs);
        }
        return list;
    }
    }
